Namespace Datos
    Public Class clPaginacion
        Dim _cmdOrg As Data.IDbCommand
        Dim _cmdPaginado As Data.IDbCommand
        Dim _cmdCuentaReg As Data.IDbCommand
        Dim _totalReg As Integer
        Public Sub New(ByVal cmd As Data.IDbCommand, Optional ByVal tipoPaginacion As enTipoPaginacion = enTipoPaginacion.SQLServer)
            _cmdOrg = cmd
            If _cmdOrg.Connection Is Nothing Then _cmdOrg.Connection = clTablaGenerica.cnnShared
            If tipoPaginacion = enTipoPaginacion.SQLServer Then
                iniciaSQL()
            ElseIf tipoPaginacion = enTipoPaginacion.Oracle Then
                iniciaOracle()
            End If
        End Sub
        Public ReadOnly Property ComandoOrg() As IDbCommand
            Get
                Return _cmdOrg
            End Get
        End Property
        Private Sub iniciaSQL()
            With _cmdOrg.CommandText.ToLower
                Dim indexFrom As Integer = .IndexOf(" from")
                Dim sqlOrder As String = .Substring(.IndexOf("order by"))
                Dim sqlInterna As String = String.Format("{0} , row_number() over ({1}) as numeroHHX {2}", .Substring(0, indexFrom), sqlOrder, .Substring(indexFrom, .IndexOf("order by") - indexFrom))
                Dim sqlPaginado As String = String.Format("select * from ({0}) as pruebaHHX where numeroHHX between @inicio and @fin", sqlInterna)
                Dim sqlCuenta As String = String.Format("select count(*) {0}", .Substring(indexFrom, .IndexOf("order by") - indexFrom))
                _cmdCuentaReg = New SqlClient.SqlCommand(sqlCuenta, _cmdOrg.Connection)
                _cmdPaginado = New SqlClient.SqlCommand(sqlPaginado, _cmdOrg.Connection)
            End With
            'agregarParametros
            copiarParametrosSQL(_cmdOrg, _cmdPaginado)
            CType(_cmdPaginado, SqlClient.SqlCommand).Parameters.Add("@inicio", SqlDbType.Int)
            CType(_cmdPaginado, SqlClient.SqlCommand).Parameters.Add("@fin", SqlDbType.Int)
            copiarParametrosSQL(_cmdOrg, _cmdCuentaReg)
        End Sub
        Private Sub iniciaOracle()
            With _cmdOrg.CommandText.ToLower
                Dim indexFrom As Integer = .IndexOf(" from")
                'modificacion Rafael
                Dim sqlPaginado As String = String.Format("select * from ({0} from ({1}))  where r between :inicio and :fin", .Substring(0, indexFrom) & ", rownum as r", .ToString)
                Dim sqlCuenta As String = String.Format("select count(*) {0}", .Substring(indexFrom, .IndexOf("order by") - indexFrom))
                _cmdCuentaReg = New OracleClient.OracleCommand(sqlCuenta, _cmdOrg.Connection)
                _cmdPaginado = New OracleClient.OracleCommand(sqlPaginado, _cmdOrg.Connection)
            End With
            copiarParametrosOledb(_cmdOrg, _cmdPaginado)
            CType(_cmdPaginado, OracleClient.OracleCommand).Parameters.Add(":inicio", OracleClient.OracleType.Int32)
            CType(_cmdPaginado, OracleClient.OracleCommand).Parameters.Add(":fin", OracleClient.OracleType.Int32)
            copiarParametrosOledb(_cmdOrg, _cmdCuentaReg)
        End Sub
        Private Sub copiarParametrosOledb(ByVal cmdOrg As OracleClient.OracleCommand, ByRef cmdDest As OracleClient.OracleCommand)
            Dim p As OracleClient.OracleParameter
            For Each p In cmdOrg.Parameters
                cmdDest.Parameters.Add(p.ParameterName, p.OracleType).Value = p.Value
            Next
        End Sub
        Private Sub copiarParametrosSQL(ByVal cmdOrg As Data.SqlClient.SqlCommand, ByRef cmdDest As Data.SqlClient.SqlCommand)
            Dim p As SqlClient.SqlParameter
            For Each p In cmdOrg.Parameters
                cmdDest.Parameters.Add(p.ParameterName, p.SqlDbType).Value = p.Value
            Next
        End Sub
        Public Function getTotalRegistros(Optional ByVal forzarRecalcular As Boolean = False)
            If _totalReg = 0 OrElse forzarRecalcular Then
                Dim cerrar As Boolean = False
                If _cmdCuentaReg.Connection.State = ConnectionState.Closed Then
                    _cmdCuentaReg.Connection.Open()
                    cerrar = True
                End If
                'modificacion Rafael
                'Dim lector As Data.SqlClient.SqlDataReader = _cmdCuentaReg.ExecuteReader
                Dim lector As Data.IDataReader = _cmdCuentaReg.ExecuteReader
                _totalReg = 0
                If lector.Read Then
                    If TypeOf _cmdCuentaReg Is SqlClient.SqlCommand Then
                        _totalReg = lector.GetInt32(0)
                    ElseIf TypeOf _cmdCuentaReg Is OracleClient.OracleCommand Then
                        _totalReg = lector.GetDecimal(0)
                    End If

                End If
                    lector.Close()
                    If cerrar Then _cmdCuentaReg.Connection.Close()
                End If
                Return _totalReg
        End Function

        Public Function getLectorPaginado(ByVal regInicio As Integer, ByVal totalPorPagina As Integer) As IDataReader
            Dim regFin As Integer = regInicio + totalPorPagina - 1
            If TypeOf _cmdPaginado Is SqlClient.SqlCommand Then
                CType(_cmdPaginado, SqlClient.SqlCommand).Parameters("@inicio").Value = regInicio
                CType(_cmdPaginado, SqlClient.SqlCommand).Parameters("@fin").Value = regFin
            ElseIf TypeOf _cmdPaginado Is OracleClient.OracleCommand Then
                '***********************Agregado por Rafael*********************************************                
                CType(_cmdPaginado, OracleClient.OracleCommand).Parameters(":inicio").Value = regInicio
                CType(_cmdPaginado, OracleClient.OracleCommand).Parameters(":fin").Value = regFin
                '****************************************************************************************
            End If
            Dim lector As IDataRecord = _cmdPaginado.ExecuteReader
            Return lector
        End Function
        Public Shared Function paginaAFirstRow(ByVal pagina As Integer, ByVal totalPorPagina As Integer) As Integer
            Return (pagina * totalPorPagina) - (totalPorPagina - 1)

        End Function
        Public Function getDataTablePaginado(ByVal regInicio As Integer, ByVal totalPorPagina As Integer) As Data.DataTable
            Dim dt As New Data.DataTable
            Dim regFin As Integer = regInicio + totalPorPagina - 1
            If TypeOf _cmdPaginado Is SqlClient.SqlCommand Then
                CType(_cmdPaginado, SqlClient.SqlCommand).Parameters("@inicio").Value = regInicio
                CType(_cmdPaginado, SqlClient.SqlCommand).Parameters("@fin").Value = regFin
                Dim da As New SqlClient.SqlDataAdapter(_cmdPaginado)
                da.Fill(dt)
            ElseIf TypeOf _cmdPaginado Is OracleClient.OracleCommand Then
                '***********************Agregado por Rafael*********************************************
                CType(_cmdPaginado, OracleClient.OracleCommand).Parameters(":inicio").Value = regInicio
                CType(_cmdPaginado, OracleClient.OracleCommand).Parameters(":fin").Value = regFin
                Dim da As New OracleClient.OracleDataAdapter(_cmdPaginado)
                da.Fill(dt)
                '***************************************************************************************
            End If
            Return dt
        End Function
    End Class
    Public Enum enTipoPaginacion
        SQLServer = 1
        Oracle = 2
    End Enum
End Namespace
